package com.controller;

import java.sql.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import com.model.Autor;
import com.model.HistorialRepertorio;
import com.model.Imagen;
import com.model.Instrumento;
import com.model.Repertorio;
import com.model.TipoInstrumento;
import com.model.Partitura;
import com.util.HibernateUtil;

public class ControladorPartitura extends HibernateUtil {

@SuppressWarnings("unchecked")
public List<Instrumento> listaInstrumentos(int id){
		
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		List<Instrumento> instrumentos = null;
		try {
			instrumentos = (List<Instrumento>)session.createQuery("from Instrumento as inst where inst.idTipo="+id).list();
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}
		session.getTransaction().commit();
		return instrumentos;
	}

@SuppressWarnings("unchecked")
public List<TipoInstrumento> listaFamilias(){
	
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<TipoInstrumento> familias = null;
	try {
		familias = (List<TipoInstrumento>)session.createQuery("from TipoInstrumento").list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return familias;
}

@SuppressWarnings("unchecked")
public List<Autor> listaAutores(){
	
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Autor> autores = null;
	try {
		autores = (List<Autor>)session.createQuery("from Autor").list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return autores;
}

@SuppressWarnings("unchecked")
public List<Repertorio> listaRepertorios(){
	
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Repertorio> repertorios = null;
	try {
		repertorios = (List<Repertorio>)session.createQuery("from Repertorio").list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return repertorios;
}


@SuppressWarnings("unchecked")
public List<Partitura> listaPartituras(Integer familia, Integer instrumento, Integer autor){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Partitura> partituras = null;
	String query = "select distinct new Partitura(P.idPartitura, P.nombre, P.anho, P.descripcion) from Partitura P, Imagen IP, PartituraAutor PA";
    String where = " where ";
    boolean bol = false;
	try {
		if(autor!=-1){
			query += where + "PA.idAutor = "+autor+" and PA.idPartitura = P.idPartitura";
			bol = true;
		}
		if(familia!=-1){
			if(instrumento!=-1){
				if(bol==false){
					query += where + "IP.idInstrumento ="+instrumento+" and IP.idPartitura = P.idPartitura";
					bol = true;
				}else{
					query += " and IP.idInstrumento ="+instrumento+" and IP.idPartitura = P.idPartitura";
				}
			}else{
				if (bol==false){
					query = "select distinct new Partitura(P.idPartitura, P.nombre, P.anho, P.descripcion) from Partitura P, Imagen IP, Instrumento I " +
							"where P.idPartitura = IP.idPartitura and I.idInstrumento = IP.idInstrumento and I.idTipo ="+familia;
					bol = true;
				}else{
					query = "select distinct new Partitura(P.idPartitura, P.nombre, P.anho, P.descripcion) from Partitura P, Imagen IP, Instrumento I, PartituraAutor PA " +
							"where P.idPartitura = IP.idPartitura and I.idInstrumento = IP.idInstrumento and PA.idPartitura = P.idPartitura and I.idTipo ="+familia+" and PA.idAutor = "+autor;
				}
			}
		}	
		partituras = (List<Partitura>)session.createQuery(query).list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return partituras;
}

@SuppressWarnings("unchecked")
public List<Partitura> listaPartiturasParaRepertorio(Integer familia, Integer instrumento, Integer autor, Integer repertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Partitura> partituras = null;
	try {
		partituras = (List<Partitura>)session.createQuery("select distinct new Partitura(P.idPartitura, P.nombre, P.anho, P.descripcion) from Partitura P, Imagen IP, PartituraAutor PA where P.idPartitura = IP.idPartitura and PA.idPartitura = P.idPartitura and IP.idInstrumento ="+instrumento+" and PA.idAutor ="+autor+" and P.idPartitura not in (select H.idPartitura from HistorialRepertorio H, Imagen I where H.idInstrumento = I.idInstrumento and H.idRepertorio ="+repertorio+" and H.idInstrumento = "+instrumento+")").list();
	} catch (HibernateException e) {
		e.printStackTrace();
	}
	session.getTransaction().commit();
	return partituras;
}

@SuppressWarnings("unchecked")
public List<Imagen> listaIdImagenes(int partitura, int instrumento, int familia){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Imagen> imagenes = null;
	try {
		if(instrumento!=-1){
		 imagenes = (List<Imagen>)session.createQuery("select idImagen from Imagen where idPartitura="+partitura+" and idInstrumento="+instrumento).list();
		}else{
			if(familia!=-1){
				imagenes = (List<Imagen>)session.createQuery("select IP.idImagen from Imagen IP,Instrumento I where IP.idInstrumento=I.idInstrumento and IP.idPartitura ="+partitura+" and I.idTipo ="+familia).list();
			}else{
				imagenes = (List<Imagen>)session.createQuery("select idImagen from Imagen where idPartitura="+partitura).list();
			}
		}
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return imagenes;
}
public Imagen listaImagenes(int idImagen){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	Imagen imagenes = null;
	try {
		imagenes = (Imagen)session.createQuery("from Imagen where idImagen="+idImagen).uniqueResult();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return imagenes;
}

@SuppressWarnings("unchecked")
public List<Partitura> listaPartiturasRepertorio(Integer repertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Partitura> partituras = null;
	try {
		partituras = (List<Partitura>)session.createQuery("select distinct new Partitura(P.idPartitura, P.nombre, P.anho, P.descripcion) from Partitura P, HistorialRepertorio HR " +
				"where P.idPartitura = HR.idPartitura and HR.idRepertorio ="+repertorio).list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return partituras;
}

@SuppressWarnings("unchecked")
public List<Instrumento> listaInstrumentosRepertorio(Integer partitura, Integer repertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	List<Instrumento> instrumentos = null;
	try {
		instrumentos = (List<Instrumento>)session.createQuery("select distinct new Instrumento(I.idInstrumento, I.nombre, I.idTipo) from Instrumento I, HistorialRepertorio HR " +
				"where I.idInstrumento = HR.idInstrumento and HR.idPartitura ="+partitura+" and HR.idRepertorio="+repertorio).list();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return instrumentos;
}

public int obtenerTipoInstrumento(Integer instrumento){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	int idTipo = 0;
	try {
		idTipo = (Integer)session.createQuery("select I.idTipo from Instrumento I where I.idInstrumento ="+instrumento).uniqueResult();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return idTipo;
}

public Repertorio obtenerRepertorio(Integer idRepertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	Repertorio repertorio = new Repertorio();
	try {
		repertorio = (Repertorio)session.createQuery("from Repertorio R where R.idRepertorio ="+idRepertorio).uniqueResult();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return repertorio;
}

public HistorialRepertorio agregarPartituraRepertorio(int idPartitura, int idRepertorio, int idInstrumento, Date fecha){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	HistorialRepertorio partitura = new HistorialRepertorio(idPartitura, idRepertorio, idInstrumento, fecha);
	try {
		session.save(partitura);
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return partitura;
}

public HistorialRepertorio eliminarPartituraRepertorio(int idPartitura, int idRepertorio, int idInstrumento){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	HistorialRepertorio partitura = new HistorialRepertorio(idPartitura, idRepertorio, idInstrumento, null);
	try {
		Query query = session.createQuery("delete from HistorialRepertorio H where H.idPartitura="+idPartitura+" and H.idRepertorio="+idRepertorio+" and H.idInstrumento="+idInstrumento);
		int row = query.executeUpdate();
		if(row==0)
			  System.out.println("Doesn't delete row. Record not found .");
			else
				System.out.println("Records deleted :"+row);
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return partitura;
}

public int eliminarRepertorio(int idRepertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	try {
		Query query = session.createQuery("delete from Repertorio R where R.idRepertorio="+idRepertorio);
		int row = query.executeUpdate();
		if(row==0)
			  System.out.println("Doesn't delete row. Record not found .");
			else
				System.out.println("Records deleted :"+row);
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return idRepertorio;
}

public Repertorio ModificarRepertorio(Repertorio repertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	try {
		System.out.println(repertorio.getNombre());
		Query query = session.createQuery("update Repertorio set nombre='"+repertorio.getNombre()+"', descripcion='"+repertorio.getDescripcion()+"' where idRepertorio="+repertorio.getIdRepertorio());
		int row = query.executeUpdate();
		if(row==0)
			System.out.println("Doesn't update row. Record not found .");
		else
			System.out.println("Records updated :"+row);
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return repertorio;
}

public int obtenerIdParaRepertorio(){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	int idRepertorio = 0;
	try {
		idRepertorio= (Integer)session.createQuery("select max(R.idRepertorio)+1 from Repertorio R").uniqueResult();
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return idRepertorio;
}

public Repertorio crearRepertorio(Repertorio repertorio){
	Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	session.beginTransaction();
	try {
		session.save(repertorio);
	} catch (HibernateException e) {
		e.printStackTrace();
		session.getTransaction().rollback();
	}
	session.getTransaction().commit();
	return repertorio;
}

}
